from datetime import date, timedelta
from DataAccess.DBConnFactory import DBConnFactory
from Accounting.Routine.Settle import *
from Accounting.Routine.OpenDayOp import *

from Position.CalcDailyPosition import *
from Mark2Mkt.CalcMktValue import *


def clear_all_record_after(ref_date):
	conn = DBConnFactory().get_db_connection('ACCOUNTING')
	
	str_date = ref_date.isoformat()	
	conn.cursor().execute("delete from asset_value where (entity not like 'SC028%') and ref_date>=TO_DATE('"+str_date+"','yyyy-mm-dd')")
	conn.cursor().execute("delete from cash_chg where (entity not like 'SC028%') and ref_date>=TO_DATE('"+str_date+"','yyyy-mm-dd')")	
	conn.cursor().execute("delete from fee where (entity not like 'SC028%') and official='F' and ref_date>=TO_DATE('"+str_date+"','yyyy-mm-dd')")
	conn.cursor().execute("delete from share_chg where (parent not like 'SC028%') and ref_date>=TO_DATE('"+str_date+"','yyyy-mm-dd')")
	conn.commit()
	
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_conn.cursor().execute("delete from portfolio_mkt_val where portfolio_id<>'SC028' and ref_date>=TO_DATE('"+str_date+"','yyyy-mm-dd')")
	pke_conn.cursor().execute("delete from position where portfolio_id<>'SC028' and ref_date>=TO_DATE('"+str_date+"','yyyy-mm-dd')")
	pke_conn.cursor().execute("delete from cash_switch where portfolio_id<>'SC028' and ref_date>=TO_DATE('"+str_date+"','yyyy-mm-dd')")
	pke_conn.cursor().execute("delete from cash_dividend_flow where portfolio_id<>'SC028' and event_date>=TO_DATE('"+str_date+"','yyyy-mm-dd')")
	pke_conn.cursor().execute("delete from stock_dividend_flow where portfolio_id<>'SC028' and event_date>=TO_DATE('"+str_date+"','yyyy-mm-dd')")
	pke_conn.commit()
	
	
def incepition_setup_DOMFUND():
	conn = DBConnFactory().get_db_connection('ACCOUNTING')
	cursor = conn.cursor()
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()
	
	sql_text = '''select portfolio_id,inception_date
				from portfolio_info 
				where portfolio_id in ('SC014','SC015','SC016')'''	
	pke_cursor.execute(sql_text)
	portfs = pke_cursor.fetchall()
					
	sql_tpl = Template('''select client_id,feeder_id2,cash_amt
					from official_share_chg
					where ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
					  and feeder_id2='${FEEDER}'
					  and transaction2='SUB' ''')
	sql_tpl2 = Template('''select feeder_id2,sum(cash_amt*decode(transaction2,'SUB',1,-1))
				from official_share_chg
				where ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
				  and feeder_id2='${FEEDER}'
				group by feeder_id2''')

	sql_text2 = '''insert into asset_value
				(ref_date,entity,av,avpu,curncy,type,official)
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4,:5,:6,:7)'''					  
	sql_text3 = '''insert into cash_switch
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4)'''			  
	sql_text4 = '''insert into position
				(ref_date,portfolio_id,ticker,security_type,amount,avg_cost_price,price_currency)
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4,:5,:6,:7)'''	
				
	for it in portfs:		
		master = it[0]
		feeder = it[0]+'_F'
		d = it[1].date()
		
		cursor.execute(sql_text2,(d.isoformat(),master,0,1.0,'CNY','NAV','T'))	
		cursor.execute(sql_text2,(d.isoformat(),feeder,0,1.0,'CNY','GAV','T'))
		cursor.execute(sql_text2,(d.isoformat(),feeder,0,1.0,'CNY','NAV','T'))		
		
		#cursor.execute(sql_tpl.substitute(DATE=d.isoformat(), FEEDER=feeder))
		#for item in cursor.fetchall():
		#	client = item[0]
		#	feeder = item[1]
		#	cash_amt = item[2]
		#	#print 'debug, ', client, feeder,cash_amt
		#	open_day_purchase(d, client, cash_amt, 'CNY', feeder, master)
	
		cursor.execute(sql_tpl2.substitute(DATE=d.isoformat(), FEEDER=feeder))	
		for item in cursor.fetchall():
			cash_amt = item[1]			
			#pke_cursor.execute(sql_text3, (d.isoformat(),master,cash_amt,'CNY'))
			pke_cursor.execute(sql_text4, (d.isoformat(),master,'9999999','CASH',0,1,'CNY'))
	
	conn.commit()
	pke_conn.commit()
	print 'info, incepition_setup_DOMFUND complete...'
	
	

def run_DOMFUND(start_date):
	conn = DBConnFactory().get_db_connection('ACCOUNTING')
	cursor = conn.cursor()
	
	#clear_all_record_after(start_date)
		
	end_date = date(2012, 12, 1)
	one_day = timedelta(days=1)
	trade_date = start_date
	
	sql_tpl = Template('''select client_id,'SUB',feeder_id2,
						sum(abs(cash_amt)),sum(abs(share_amt)),'',ref_date,''
						from official_share_chg
						where transaction2='SUB'
						  and ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
						  and feeder_id2 not like 'SC028%'
						group by client_id,feeder_id2,ref_date
					UNION
						select client_id,'RED',feeder_id2,
						sum(abs(cash_amt)),sum(abs(share_amt)),
							red_ref_id,red_ref_date,detail	
						from official_share_chg
						where transaction2='RED'
						  and ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
						  and feeder_id2 not like 'SC028%'
						group by client_id,feeder_id2,red_ref_id,red_ref_date,detail
						''')
						  
	sql_tpl2 = Template('''select * from (
						select id from 
						feeder_remain_share
						where child='${CLIENT}' 
						order by ref_date) 
						where rownum<=1''')
	
	while trade_date<end_date:
		print 'settlement date=', trade_date.isoformat(), '...'
		
		update_position_driven_by_dividend(trade_date)
		update_position_driven_by_transaction(trade_date)	
		update_position_driven_by_repo_expire(trade_date)
		update_daily_position(trade_date)
		
		calc_portf_mkt_val(trade_date)
		daily_clearing(trade_date)

		cursor.execute(sql_tpl.substitute(DATE=trade_date.isoformat()))
		r = cursor.fetchall()
		for act in r:
			client_id = act[0]
			tran = act[1]
			feeder_id = act[2]
			cash_amt = act[3]
			share_amt = act[4]
			master_id = feeder_id[0:5]
			
			if tran=='SUB':
				open_day_purchase(trade_date, client_id, cash_amt, 'CNY', feeder_id, master_id)
				
			else: #tran=='RED'				
				cursor.execute(sql_tpl2.substitute(CLIENT=client_id))
				t = cursor.fetchone()
				if (not t) or len(t)==0:
					print 'Can not find the corresponding sub record at ', trade_date.isoformat(), client_id
					return
				else:
					corr_sub_id = t[0]
				
				open_day_redemption(trade_date, client_id, None, 'CNY', share_amt, False, corr_sub_id)
			
		reflect_to_portf_cash_account(trade_date)
		calc_portf_mkt_val(ref_date, True)
		trade_date += one_day

		
		
def m2m_DOMFUND(start_date):
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()
	
	pke_cursor.execute('''truncate table tmp_mkt_val''')
	pke_conn.commit()
	
	sql_tpl = Template('''insert into tmp_mkt_val
					select TO_DATE('${DATE}','yyyy-mm-dd'),
						m2m.portfolio_id,'EXCEPT_CASH','ST+REPO', 
						sum(m2m.mkt_val),'CNY'
					from mark_to_mkt m2m 
					where m2m.security_type<>'CASH'
						and m2m.security_type<>'REPO'
					    and m2m.ref_date=TO_DATE('${DATE}','yyyy-mm-dd')  
					group by m2m.portfolio_id
					''')
	
	end_date = date(2012, 11, 17)
	one_day = timedelta(days=1)
	
	d = start_date	
	while d<end_date:
		pke_cursor.execute(sql_tpl.substitute(DATE=d.isoformat()))
		print d, ' completed...'
	
		pke_conn.commit()
		d += one_day
		
		


	
	